"""
数据库连接类
所有其他数据连接从此初始化

排期同步update_server6.py取消导入CentralDB

"""
import time
import random
# import pandas as pd
import json
from .ScheduleDB import SchedulerHandler
from .CGTWDB6 import CGTW6Handler
from .AD_DB import ADhandler
from .CentralDB import CSDBManager

class Handler(object):

    def __init__(self,CGTW=None,SCHEDULER=None):
        super(Handler, self).__init__()

        self.scheduler_handlder = SchedulerHandler(CGTW,SCHEDULER)
        self.cgtw_handler = CGTW6Handler(CGTW)
        self.ad_handler = ADhandler(SCHEDULER)
        self.chandler = CSDBManager('postproduction')

    """
    ***********************************************************************************
    ***********************************************************************************
    ***********************************************************************************
    ***********************************************************************************
    
    排期数据操作
    
    ***********************************************************************************
    ***********************************************************************************
    ***********************************************************************************
    ***********************************************************************************
    """


    """
    ***********************************************
    人员，部门
    ***********************************************
    """
    def get_manager_info(self):
        """
        获取cgtw管理者信息
        :return:
        """
        return self.cgtw_handler._get_manager_info()

    def get_department_info(self):
        """
        获取部门信息,把常用的一次性输出
        :return: 返回
        1.以部门名称为键,data为值的所有部门字典  {'01美术':{id,cg_id,name,display}}
        2.display为1的显示部门的信息,  {'01美术':{id,cg_id,name,display}}
        3.显示部门的名称列表  ['13虚拟', '02模型', '03跟踪', '05场景', '07特效二', '08合成二', '12TD', '01美术', '06灯光',
        '09包装', '10剪辑', '04动画', '08合成一', '11调色', '07特效一']
        """
        department_info, department_display_info, departments = self.scheduler_handlder.r_department_info()

        return department_info, department_display_info, departments

    def display_departments(self):
        """
        获取需要显示的部门信息
        :return: ['01美术','02跟踪',...]
        """
        department_info, department_display_info, departments = self.get_department_info()

        return departments

    def get_display_department_artists(self):
        """
        获取显示部门的部门人员列表
        :return: {'01美术':['吴子昱','王倩楠',...],'02跟踪':['xx']}
        """
        department_artists = {}
        r_account_info = self.scheduler_handlder.get_r_account_info()  # {conf_department_id:[data,data],}
        department_info, department_display_info, departments = self.scheduler_handlder.r_department_info()

        for k, v in department_display_info.items():
            conf_department_id = v['cg_id']
            artist_data_list = r_account_info.get(conf_department_id, [])
            if artist_data_list:
                artist_list = []
                for artist_data in artist_data_list:
                    artist = artist_data['artist']
                    artist_list.append(artist)
                department_artists[k] = artist_list

        return department_artists

    def get_display_department_accout_ids(self):
        """
        获取显示部门的部门人员id列表
        :return: {'01美术':['accout_id','accout_id',...],'02跟踪':['xx']}
        """
        department_account_ids = {}
        r_account_info = self.scheduler_handlder.get_r_account_info()  # {conf_department_id:[data,data],}
        department_info, department_display_info, departments = self.scheduler_handlder.r_department_info()

        for k, v in department_display_info.items():
            conf_department_id = v['cg_id']
            artist_data_list = r_account_info.get(conf_department_id, [])
            if artist_data_list:
                account_id_list = []
                for artist_data in artist_data_list:
                    account_id = artist_data['cg_id']
                    account_id_list.append(account_id)
                department_account_ids[k] = account_id_list

        return department_account_ids

    def get_department_artists_level_map(self):
        """
        获取各部门的人员等级信息
        :return: {'01美术':{'吴子昱':'Px',...}}
        """
        department_artists = self.get_display_department_artists()
        artist_level_map = self.scheduler_handlder.artist_level_map()

        department_artists_level_map = {}
        for dep, artist_list in department_artists.items():
            level_map = {}
            for artist in artist_list:
                level_map[artist] = artist_level_map.get(artist, '')

            department_artists_level_map[dep] = level_map

        return department_artists_level_map

    def get_account_id_artist_map(self,r_account_info):

        return self.scheduler_handlder.get_account_id_artist_map(r_account_info)

    def get_artist_level_map(self,r_account_info=None):
        """
        获取所有人的等级表
        :param r_account_info: r_account 表信息
        :return: {'吴子昱':'Px',...}
        """


        return self.scheduler_handlder.get_artist_level_map(r_account_info)



    def get_account_id_level_map(self,r_account_info):
        """
        获取所有人员id与等级表
        :return: {'account_id:'Px',...}
        """
        return self.scheduler_handlder.get_account_id_level_map(r_account_info)

    def get_r_account_artist_map_in_display_departments(self):
        """
        获取：
        department_id_name_map:{'部门id':'部门名称',...}
        account_id_artist_map:{'人员id':'人员名称',...}
        account_id_department_map:{'人员id':'部门名称',...}
        :return:
        """
        return self.scheduler_handlder.r_account_artist_map_in_display_departments()


    def get_pipeline_info(self):
        """
        获取阶段信息
        :return:
        """
        pipeline_id_info, id_pipeline_info = self.cgtw_handler._get_pipeline_info()

        return pipeline_id_info, id_pipeline_info

    """
    ***********************************************
    任务
    ***********************************************
    """
    def get_new_tasks(self,databases,departments):
        """
        获取指定database与部门的 新任务
        :param databases:
        :param departments:
        :return:
        """
        data_list = self.scheduler_handlder.filter_r_task_info(databases,departments)
        return data_list


    def get_tasks_in_dates(self,start_date,end_date,account_id=None):
        """
        根据传入的日期时间段，返回该时间段内的所有任务,包括cgtw任务与custom任务
        注: show_workhour为空的任务会被过滤掉，为0的可以被读取到
        :param start_date:
        :param end_date:
        :param account_id: 指定人员id则单独查询该人员的任务
        :return:
        """

        tasks = self.scheduler_handlder.task_in_dates(start_date, end_date, account_id)
        return tasks

    def get_tasks_in_departments_dates(self,departments,start_date,end_date):
        """
        根据传入的部门，起始结束日期来获取task表里的任务
        :param departments:
        :param start_date:
        :param end_date:
        :return:
        """
        return self.scheduler_handlder.cgtwtask_in_departments_dates(departments,start_date,end_date)


    def get_custom_tasks_in_departments_dates(self,departments,start_date,end_date):
        """
        根据传入的部门，起始结束日期来获取custom_task表里的任务
        :param departments:
        :param start_date:
        :param end_date:
        :return:
        """
        return self.scheduler_handlder.customtask_in_departments_dates(departments,start_date,end_date)


    def get_group_tasks_in_departments_dates(self,departments,start_date,end_date):
        """
        根据传入的部门，起始结束日期来获取custom_task表里的任务
        :param departments:
        :param start_date:
        :param end_date:
        :return:
        """
        return self.scheduler_handlder.grouptask_in_departments_dates(departments,start_date,end_date)



    def get_all_department_per_project_map(self,start_date,end_date):
        """
        传入获取到所有任务信息，根据部门来划分各项目的任务信息
        :param start_date,end_date: 选择的起始与结束时间段
        :return:{'01美术':{'proj_ylm':[task1,task2,...],'02模型':{'proj_ylm':[task1,task2,task3...],...}}
        最后通过database映射项目名称
        """
        all_department_per_project_map = {}
        department_tasks = {}
        all_task_info_list = self.get_tasks_in_dates(start_date, end_date)
        if all_task_info_list:
            for task in all_task_info_list:
                department = task['department']
                if department not in department_tasks.keys():
                    department_tasks[department] = [task]
                else:
                    department_tasks[department].append(task)
            for dep, ts in department_tasks.items():
                db_task = {}
                for t in ts:
                    db = t['database']
                    if db not in db_task.keys():
                        db_task[db] = [t]
                    else:
                        db_task[db].append(t)
                all_department_per_project_map[dep] = db_task

        return all_department_per_project_map

    def get_all_department_overtime_task(self,start_date,end_date,all_department_per_project_map=None):
        """
        获取指定时间段内的延期任务
        注： 延期任务必定是自定义任务，即 task_type = 'custom'
            延期任务的 custom_type = '超时'
        :param start_date,end_date: 起始、结束日期
        :param all_department_per_project_map:
        :return: {'01美术':{'proj_xxss':[taskdata,taskdata,...],'proj_my':[...]},'02跟踪':..}}
        """
        department_overtime_task_map = {}
        department_per_project_map = all_department_per_project_map if all_department_per_project_map else self.get_all_department_per_project_map(
            start_date, end_date)

        if department_per_project_map:
            for department, data in department_per_project_map.items():
                database_overtime_task_map = {}
                for database, task_list in data.items():
                    overtime_task_list = []
                    for task in task_list:
                        # print('task',task)
                        if task['task_type'] == 'custom':
                            if task['custom_type'] == '超时':
                                overtime_task_list.append(task)
                    database_overtime_task_map[database] = overtime_task_list
                department_overtime_task_map[department] = database_overtime_task_map

        return department_overtime_task_map

    def get_all_department_approve_task(self,start_date,end_date,all_department_per_project_map=None):
        """
        获取指定时间段内的通过的任务,任务状态为Approve、暂停、客户通过
        注： 仅计算cgtw类型任务，不计算cutom类型任务。

        :param start_date,end_date: 起始、结束日期
        :param all_department_per_project_map:
        :return: {'01美术':{'proj_xxss':[taskdata,taskdata,...],'proj_my':[...]},'02跟踪':..}}
        """
        department_approve_task_map = {}
        department_per_project_map = all_department_per_project_map if all_department_per_project_map else self.get_all_department_per_project_map(
            start_date, end_date)

        if department_per_project_map:
            for department, data in department_per_project_map.items():
                database_approve_task_map = {}
                for database, task_list in data.items():
                    approve_task_list = []
                    for task in task_list:
                        # print('task',task)
                        if task['task_type'] == 'cgtw':
                            if task['status'] in ['Approve', '暂定', '客户通过']:
                                approve_task_list.append(task)
                    database_approve_task_map[database] = approve_task_list
                department_approve_task_map[department] = database_approve_task_map

        return department_approve_task_map

    """
        ************************************************************************
        ************************************************************************
        ************************************************************************
        ************************************************************************

        cgtw6数据操作

        ************************************************************************
        ************************************************************************
        ************************************************************************
        ************************************************************************
    """



    """
    ***********************************************
    r_task表
    ***********************************************
    """

    def get_cgtw_tasks(self,schema):
        """
        根据传入schema名称获取其所有任务
        :param schema:
        :return:
        """
        r_tasks = []
        cgtw_tasks = self.cgtw_handler.get_cgtw_task_info(schema)
        time_connect, time_cursor = self.scheduler_handlder._init_timeaxis_db()
        if cgtw_tasks:
            try:
                for data in cgtw_tasks:
                    cg_id = data['cg_id']
                    sql = "select client_status_retake_count,assigned from timeaxis.r_task where cg_id ='{}'".format(
                        cg_id)
                    time_cursor.execute(sql)
                    result = time_cursor.fetchone()
                    local_count, assigned = 0, 0
                    if result:
                        local_count, assigned = result
                    data['assigned'] = assigned
                    if data['status'] == '客户返修':  # 定时同步，现在只同步客户返修状态的，如果返修后立马提交了，在手动提交中同步
                        if data['client_status_retake_count'] and data[
                            'client_status_retake_count'] > local_count:  # 只有当有数值时才算，过滤掉通用素材
                            data['assigned'] = 0
                            data['workhour'] = data['last_workhour']
                    r_tasks.append(data)
            except Exception as get_cgtw_tasks_ERR:
                print('get_cgtw_tasks_ERR:', str(get_cgtw_tasks_ERR))
            finally:
                time_connect.close()
        return r_tasks

    """
        ************************************************************************
        ************************************************************************
        ************************************************************************
        ************************************************************************

        更新token

        ************************************************************************
        ************************************************************************
        ************************************************************************
        ************************************************************************
    """

    def update_token(self,login_data = None,token=None):
        """
        更新public_conf的token
        :param login_data: 账户登录信息,如果步输入，则通过CONFIG.py中的登录账户获取token信息
        :param token: 如果主动传入token,则直接更新token
        :return:
        """
        token = token if token else self.scheduler_handlder.get_token(login_data)
        if token:
            try:
                sql = "update timeaxis.public_conf set token='{}' ".format(token)
                time_connect, time_cursor = self.scheduler_handlder._init_timeaxis_db()
                time_cursor.execute(sql)
                time_connect.commit()
                print('更新token成功')
            except Exception as update_token_ERR:
                print("更新token失败")
                print(str(update_token_ERR))
            finally:
                time_connect.close()


    """
        ************************************************************************
        ************************************************************************
        ************************************************************************
        ************************************************************************
       
        同步数据操作
        
        ************************************************************************
        ************************************************************************
        ************************************************************************
        ************************************************************************
    """

    """
    ***********************************************
    r_account表
    ***********************************************
    """
    def sync_r_account(self,force=False):
        """
        同步cgtw人员信息
        :param: force,默认False，即直接读取内存中的账户信息；如果设置为True，则即时读取cgtw用户数据，用在动态同步部门人员命令上
        :return:
        """
        account_info = self.cgtw_handler.account_info
        if force:
            account_info, _, __ = self.cgtw_handler.get_cgtw_account_info()
        if account_info:
            print(len(account_info))
            # print(account_info)
            try:
                time_connect, time_cursor = self.scheduler_handlder._init_timeaxis_db()
                for account_id, data in account_info.items():
                    sql = "insert into timeaxis.r_account (cg_id,department_id,level,artist,active_department) values ('{0}','{1}','{2}','{3}','{4}') " \
                          "on conflict(cg_id) do update set cg_id='{0}',department_id='{1}',level='{2}',artist='{3}',active_department='{4}'" \
                        .format(data['cg_id'], data['department_id'], data['level'], data['artist'],
                                data['active_department'])
                    time_cursor.execute(sql)
                time_connect.commit()
                print('sync_r_account 完成')
            except Exception as sync_r_account_ERR:
                print('sync_r_account_ERR:', str(sync_r_account_ERR))
            finally:
                time_connect.close()


    """
    ***********************************************
    r_department表
    ***********************************************
    """
    def sync_r_department(self,force=False):
        """
        同步cgtw部门信息
        :param: force,默认False，即直接读取内存中的账户信息；如果设置为True，则即时读取cgtw用户数据，用在动态同步部门人员命令上
        :return:
        """
        id_department_info = self.cgtw_handler.id_department_info
        if force:
            _, id_department_info = self.cgtw_handler._get_department_info()
        if id_department_info:
            # print(id_department_info)
            try:
                time_connect, time_cursor = self.scheduler_handlder._init_timeaxis_db()
                for department_id , name in id_department_info.items():
                    sql = "insert into timeaxis.r_department(cg_id,name) values ('{0}','{1}') on conflict(cg_id) " \
                          "do update set cg_id='{0}',name='{1}'".format(
                        department_id, name)
                    time_cursor.execute(sql)
                time_connect.commit()
                print('sync_r_department 完成')
            except Exception as sync_r_department_ERR:
                print('sync_r_department_ERR:',str(sync_r_department_ERR))
            finally:
                time_connect.close()




    """
    ***********************************************
    r_project表
    ***********************************************
    """
    def sync_r_project(self):
        """
        更新 r_project表
        :return:
        """
        database_project_info = self.cgtw_handler.database_project_info #{'proj_by':data,'proj_lhy':data,...}
        # print(database_project_info)
        if database_project_info:
            try:
                time_connect, time_cursor = self.scheduler_handlder._init_timeaxis_db()

                for database,data in database_project_info.items():
                    sql = "insert into timeaxis.r_project (cg_id,database,entity_name,full_name,color," \
                          "status,resolution,frame_rate,path,eps," \
                          "project_material_path) values " \
                          "('{0}','{1}','{2}','{3}','{4}'," \
                          "'{5}','{6}','{7}','{8}','{9}'," \
                          "'{10}')" \
                          "on conflict(cg_id) do update set database='{1}',entity_name='{2}',full_name='{3}',color='{4}'," \
                          "status='{5}',resolution='{6}',frame_rate='{7}',path='{8}',eps='{9}'," \
                          "project_material_path = '{10}'" \
                        .format(data['cg_id'], data['database'], data['entity_name'], data['full_name'],data['color'],
                                data['status'], data['resolution'], data['frame_rate'], data['path'],data['eps'],
                                data['project_material_path'])
                    time_cursor.execute(sql)
                time_connect.commit()
                print('sync_r_project 完成')
            except Exception as sync_r_project_ERR:
                print('sync_r_project_ERR :',str(sync_r_project_ERR))
            finally:
                time_connect.close()





    """
    ***********************************************
    r_task表
    ***********************************************
    """



    def sync_r_tasks(self,schema,task_id_list=None):
        """
        根据传入schema名称获取其所有任务并更新，
        无人名任务被过滤掉
        :param schema:
        :param task_id_list: 限定范围中cgtw endity表中任务id
        :return:
        """
        cgtw_tasks = self.cgtw_handler.get_cgtw_task_info(schema,task_id_list)
        time_connect, time_cursor = self.scheduler_handlder._init_timeaxis_db()
        cgtw_connect, cgtw_cursor = self.cgtw_handler._init_cgtw_db()
        current_data = None

        if cgtw_tasks:
            try:
                count = 0
                task_count = len(cgtw_tasks)
                for data in cgtw_tasks:
                    current_data = data
                    cg_id = data['cg_id']
                    sql = "select client_status_retake_count,assigned from timeaxis.r_task where cg_id ='{}'".format(cg_id)
                    time_cursor.execute(sql)
                    result = time_cursor.fetchone()
                    local_count, assigned = 0, 0
                    if result:
                        local_count, assigned = result
                    data['assigned'] = assigned
                    status = data.get('status','')
                    if status and status == '客户返修':#定时同步，现在只同步客户返修状态的，如果返修后立马提交了，在手动提交中同步
                        if data['client_status_retake_count'] and data['client_status_retake_count'] > local_count:#只有当有数值时才算，过滤掉通用素材
                            data['assigned'] = 0
                            data['workhour'] = data['last_workhour']
                    self.add_R_task(time_cursor,data)  #cgtw任务更新到r_task表
                    try: #更新本地task表
                        self.update_task(time_cursor,cgtw_connect,cgtw_cursor,data, data['id'])
                    except Exception as update_assigned_task_ERR:
                        print('尝试更新排序系统已分配任务失败')
                        print(str(update_assigned_task_ERR))
                    count += 1

                    print(f'{count}/{task_count}')
                time_connect.commit()

            except Exception as sync_r_tasks_ERR:
                print('sync_r_tasks_ERR:',str(sync_r_tasks_ERR),current_data)
            finally:
                time_connect.close()
                cgtw_connect.close()


    def add_R_task(self,time_cursor,data):
        """
        更新r_task表
        :param data:
        :return:
        """
        sql = "insert into timeaxis.r_task (cg_id,description,name,eps,img_path," \
                                 "frame,assign_pipeline,shot_difficulty_level,deadline,artist," \
                                 " module, task_name, status,client_status_retake_count,assigned," \
                                 "database,difficulty_level,important_task_mark,workhour,last_workhour," \
                                 "department,retake_workhour,last_submit_time,task_priority,task_pipeline," \
              "pinyin,account_id)" \
              " values ('{0}','{1}','{2}','{3}','{4}'," \
              "'{5}','{6}','{7}','{8}','{9}'," \
              "'{10}','{11}','{12}',{13},{14}," \
              "'{15}','{16}','{17}','{18}','{19}'," \
              "'{20}','{21}','{22}','{23}','{24}'," \
              "'{25}','{26}')" \
              "on conflict(cg_id) do update set cg_id ='{0}',description='{1}',name='{2}',eps='{3}',img_path='{4}'," \
              "frame='{5}',assign_pipeline='{6}',shot_difficulty_level='{7}',deadline='{8}',artist='{9}'," \
              "module='{10}',task_name='{11}',status='{12}',client_status_retake_count= {13},assigned= {14}," \
              "database='{15}',difficulty_level='{16}',important_task_mark='{17}',workhour='{18}',last_workhour='{19}'," \
              "department='{20}',retake_workhour='{21}',last_submit_time='{22}',task_priority='{23}',task_pipeline='{24}'," \
              "pinyin='{25}',account_id='{26}'" \
            .format(data['cg_id'],data['description'],data['name'],data['eps'],data['img_path'],
                    data['frame'],data['assign_pipeline'],data['shot_difficulty_level'],data['deadline'],data['artist'],
                    data['module'],data['task_name'],data['status'],data['client_status_retake_count'],data['assigned'],
                    data['database'],data['difficulty_level'],data['important_task_mark'],data['workhour'],data['last_workhour'],
                    data['department'],data['retake_workhour'],data['last_submit_time'],data['task_priority'],data['task_pipeline'],
                    data['pinyin'],data['account_id'])
        try:
            time_cursor.execute(sql)
        except Exception as add_R_task_ERR:
            print('add_R_task_ERR:',str(add_R_task_ERR),data['cg_id'])
            print(data)
            # time_connect.commit()
            pass


    def update_task(self,time_cursor,cgtw_connect,cgtw_cursor,data,cgtw_task_id):
        """
        更新已分配任务。如果该任务已经分配了，对比一下人名是否一致,
        如果人名一致：
            那么就是同任务只需更新状态与工时，但是得注意，是否为拆分任务。拆分过的任务只更新真实工时，不更新拆分工时。切记！
        如果人名不一致：
            (那么是换人了)
            判定状态是否为Wait即未开始制作，
                如果是Wait那么直接将该任务删除,并将r_task里assigned重置为0，因为update_task在是synchronized_R_task里，
                前面已经同步了r_task.同时将cgtw该任务的起始、结束日期清空
                如果不是Wait，那么表示该任务前一个人已经在做了，就创建一个对应的custom任务，并将该timeaxis.task任务删除
                同时将cgtw该任务的起始、结束日期清空
        注意：永远不要管show_workhour，它在TaskItem中自动计算
        :param data: 从cgtw中解析出的新所有数据
        :param cgtw_task_id: cgtw task id
        :return:
        """
        select_sql = "select id,cg_id,start_date,end_date,artist," \
                     "workhour,description,note,position,show_workhour," \
                     "show_name,assigned_time,department,project_name,date_length," \
                     "ignore_weekend,status,project_color,database,assigner," \
                     "separated " \
                     "from timeaxis.task where cg_id ='{}'".format(data['cg_id'])
        time_cursor.execute(select_sql)
        row = time_cursor.fetchone()
        if row: #当是在排序系统分配过的任务时
            task_id,cg_id,start_date,end_date,artist,\
            workhour,description,note,position,show_workhour,\
            show_name,assigned_time,department,project_name,date_length,\
            ignore_weekend,status,project_color,database,assigner,\
            separated = row
            ignore_weekend = ignore_weekend if ignore_weekend else 0
            if artist == data['artist']:#不换人
                new_show_name = data['name'] + '-' + data['task_name']
                # if not separated:
                # update_status_workhour_sql = "update timeaxis.task set status = '{}',workhour ='{}',show_workhour='{}'," \
                #                              "task_name='{}',task_pipeline='{}',show_name='{}' where id ={}". \
                #     format(data['status'], data['workhour'], data['workhour'],data['task_name'], data['task_pipeline'], new_show_name,
                #            task_id)  # 这里通过id来直接更改信息
                # else:
                update_status_workhour_sql = "update timeaxis.task set status = '{}',workhour ='{}'," \
                                             "task_name='{}',task_pipeline='{}',show_name='{}'," \
                                             "difficulty_level='{}',img_path='{}',eps='{}' where id ={}". \
                    format(data['status'], data['workhour'], data['task_name'], data['task_pipeline'], new_show_name,
                           data['difficulty_level'],data['img_path'],data['eps'],task_id)  # 这里通过id来直接更改信息
                try:
                    time_cursor.execute(update_status_workhour_sql)
                except Exception as update_status_workhour_ERR:
                    print('update_status_workhour_ERR:',str(update_status_workhour_ERR))
                    print('当前已分配任务数据:', row)
                    print('不换人，更新已分配任务失败')

            elif artist != data['artist']: #换人了
                delete_sql = "delete from timeaxis.task where id={}".format(task_id)  #删除原任务
                reset_rtask_sql = "update timeaxis.r_task set assigned =0 where cg_id='{}'".format(data['cg_id'])
                if status == 'Wait':  #当任务还没开始做时，直接删除原task任务
                    try:
                        time_cursor.execute(delete_sql)
                        time_cursor.execute(reset_rtask_sql)
                    except Exception as delete_or_reset_rtask_ERR:
                        print('任务未提交，删除已分配任务或者重置rtask分配状态失败')
                        print(str(delete_or_reset_rtask_ERR))
                else:#如果当前任务已经开始做了，那么创建一个自定义任务描述它，排除任务组先
                    if position != 1000:
                        task_type = 'custom'
                        custom_type = '其他(有工时)'
                        parent_data={}
                        color=''
                        font_color='#1C1C1C'#默认已分配
                        c_sql = "insert into timeaxis.custom_task (cg_id,start_date,end_date,artist,workhour," \
                                "description,task_type,custom_type,note,position," \
                                "show_workhour,parent_data,show_name,assigned_time,department," \
                                "project_name,color,date_length,ignore_weekend,font_color," \
                                "database,assigner) values " \
                              "('{0}','{1}','{2}','{3}','{4}'," \
                                "'{5}','{6}','{7}','{8}',{9}," \
                                "'{10}','{11}','{12}','{13}','{14}'," \
                                "'{15}','{16}',{17},{18},'{19}'," \
                                "'{20}','{21}')". \
                            format(cg_id, start_date, end_date, artist, workhour,
                                   description,task_type, custom_type, note, position,
                                   show_workhour,json.dumps(parent_data), show_name, assigned_time, department,
                                   project_name, color, date_length, ignore_weekend, font_color,
                                   database,assigner)
                        try:
                            time_cursor.execute(delete_sql)
                            time_cursor.execute(reset_rtask_sql)
                            time_cursor.execute(c_sql)#创建一个新的custom任务
                        except Exception as delete_or_reset_rtask_or_create_new_custom_ERR:
                            print('任务已提交,删除或重置rtask分配状态或者创建新自定义任务失败')
                            print(str(delete_or_reset_rtask_or_create_new_custom_ERR))

                self.clear_dates(cgtw_connect,cgtw_cursor,database,cgtw_task_id)  #不管什么状态，只要换人了，就清空当前cgtw任务的日期


    def clear_dates(self,cgtw_connect,cgtw_cursor,database,cgtw_task_id):
        """
        清空cgtw上的日期
        :param database:
        :param cgtw_task_id:
        :return:
        """
        select_sql = "select json_data from {0}.entity where id='{1}'".format(database, cgtw_task_id)
        try:
            cgtw_cursor.execute(select_sql)
            result = cgtw_cursor.fetchone()
            if result:
                json_data = result[0]
                json_data['start_date'] = ''
                json_data['end_date'] = ''
                update_sql = "update {0}.entity set json_data='{1}' where id='{2}'".format(database,
                                                                                         json.dumps(json_data),
                                                                                         cgtw_task_id)
                cgtw_cursor.execute(update_sql)
                cgtw_connect.commit()
        except Exception as clear_dates_ERR:
            print("清空日期失败")
            print(str(clear_dates_ERR))


    def update_artist(self,task_id_list=None):
        """
        更新r_task的任务人名
        :param schema:
        :param task_id_list:
        :return:
        """
        time_connect, time_cursor = self.scheduler_handlder._init_timeaxis_db()
        if task_id_list:
            try:
                for cg_id in task_id_list:
                    sql = f"update timeaxis.r_task set artist = ''  where cg_id = '{cg_id}'"
                    time_cursor.execute(sql)
                time_connect.commit()
            except Exception as ERR:
                print('update_artist_ERR:',ERR)
            finally:
                time_connect.close()


    """
    ***********************************************
    task表
    ***********************************************
    """

    def clear_repeat_rtask(self,schema_list):
        """
        清空已不存在的任务
        :param schema_list: [’proj_xx‘,'proj_xx',...]
        :return:
        """
        if schema_list:
            time_connect, time_cursor = self.scheduler_handlder._init_timeaxis_db()
            cgtw_connect, cgtw_cursor = self.cgtw_handler._init_cgtw_db()
            for proj in schema_list:
                id_select_sql = "select id from {}.entity ".format(proj)
                cg_id_select_sql = "select cg_id from timeaxis.r_task where database='{}'".format(proj)
                try:
                    result = None
                    try:
                        cgtw_cursor.execute(id_select_sql)
                        result = cgtw_cursor.fetchall()
                    except Exception as no_schema_ERR:
                        print(f'{proj}不存在,忽略')
                    time_cursor.execute(cg_id_select_sql)
                    cg_id_result = time_cursor.fetchall()
                    new_data = []
                    cg_id_data = []
                    if result:
                        for i in result:
                            new_data.append(i[0])
                        print('激活项目的所有cgtw任务数量:',len(new_data))
                    if cg_id_result:
                        for c in cg_id_result:
                            cg_id = c[0]
                            task_id = cg_id.split(',')[2]
                            cg_id_data.append(task_id)
                        print('排期数据记录的所有cgtw任务数:',len(cg_id_result))

                    set_cgtw = set(new_data)
                    set_rtask = set(cg_id_data)
                    diff = list(set_rtask.difference(set_cgtw))
                    print('多余任务数:',len(diff))
                    if diff:
                        if len(diff)>1000:
                            cg_id_clip_list = self.clip_list(diff,1000)
                            for c in cg_id_clip_list:
                                connect_cg_id = '|'.join(c)
                                delete_sql = "delete from timeaxis.r_task where cg_id similar to '%({})' and database ='{}'".\
                                    format(connect_cg_id,proj)
                                try:
                                    time_cursor.execute(delete_sql)
                                    time_connect.commit()
                                    print('删除{}重复任务完成'.format(proj))
                                except Exception as delete_repeatRtask_ERR:
                                    print('删除{}重复任务失败'.format(proj))
                                    print(str(delete_repeatRtask_ERR))

                        else:
                            connect_cg_id = '|'.join(diff)
                            delete_sql = "delete from timeaxis.r_task where cg_id similar to '%({})' and database ='{}'". \
                                format(connect_cg_id, proj)
                            try:
                                time_cursor.execute(delete_sql)
                                time_connect.commit()
                                print('删除{}重复任务完成'.format(proj + '部分'))
                            except Exception as delete_repeatRtask_ERR:
                                print('删除{}重复任务失败'.format(proj))
                                print(str(delete_repeatRtask_ERR))

                except Exception as select_ERR:
                    print(str(select_ERR))

            time_connect.close()
            cgtw_connect.close()


    def clip_list(self,a, c):
        """
        列表切分
        :param a:
        :param c:
        :return:
        """
        clip_back = []
        if len(a) > c:
            for i in range(int(len(a) / c)):
                # print(i)
                clip_a = a[c * i:c * (i + 1)]
                clip_back.append(clip_a)
                # print(clip_a)
            # last
            last = a[int(len(a) / c) * c:]
            if last:
                clip_back.append(last)
        return clip_back


    def sync_tasks(self,active_databases=[],cg_id_list=[]):
        """
        同步状态
        :param: active_databases, 激活的schemal
        :param: cg_id_list, 直接传入cg_id_list，则直接对这些cg_id任务进行同步，而不需要schema.
                注意： 主动同步一定要更新r_task表中一些数据。自动更新是r_task表先更

        :return:
        """

        info_list = []
        success_task_dict = {}
        all_cg_id_list = cg_id_list

        database_cg_id_map = {}
        if not cg_id_list:  #当没有提供具体的cg_id列表时，全局查询激活的schema
            try:
                connect_database = '|'.join(active_databases)
                task_sql = "select cg_id from timeaxis.task where database similar to '({})'".format(connect_database)
                time_connect, time_cursor = self.scheduler_handlder._init_timeaxis_db()
                time_cursor.execute(task_sql)
                result = time_cursor.fetchall()
                if result:
                    for t in result:
                        cg_id = t[0]
                        all_cg_id_list.append(cg_id)
                        database, module, task_id = cg_id.split(',')
                        if database not in database_cg_id_map.keys():
                            database_cg_id_map[database] = [task_id]
                        else:
                            database_cg_id_map[database].append(task_id)
            except Exception as select_task_ERR:
                print('获取task中')
                print(str(select_task_ERR))
            finally:
                time_connect.close()
        else:   #当指定了cg_id列表，那么直接按项目schema分出database_cg_id_map
            for cg_id in cg_id_list:
                database, module, task_id = cg_id.split(',')
                if database not in database_cg_id_map.keys():
                    database_cg_id_map[database] = [task_id]
                else:
                    database_cg_id_map[database].append(task_id)


        if database_cg_id_map:
            print("database_cg_id_map 个数:",len(database_cg_id_map))
            # print(database_cg_id_map)
            updated_cg_id_list = []  # 更新过的cg_id,用于
            try:
                time_connect, time_cursor = self.scheduler_handlder._init_timeaxis_db()
                for schema,task_id_list in database_cg_id_map.items():
                    cgtw_data_list = self.cgtw_handler.get_cgtw_task_info(schema,task_id_list)
                    print(schema," 数量：",len(cgtw_data_list))
                    for data in cgtw_data_list:

                        update_task_sql = f"update timeaxis.task set status='{data['status']}'," \
                                          f"difficulty_level='{data['difficulty_level']}'," \
                                          f"workhour='{data['workhour']}'," \
                                          f"last_submit_time='{data['last_submit_time']}'," \
                                          f"cgworkhour='{data['workhour']}'," \
                                          f"eps='{data['eps']}'," \
                                          f"client_status_retake_count={data['client_status_retake_count']}," \
                                          f"img_path='{data['img_path']}'  " \
                                          f"where cg_id ='{data['cg_id']}'"


                        update_Rtask_sql = "update timeaxis.r_task set status='{}',workhour='{}',difficulty_level='{}'," \
                                           "img_path='{}' where cg_id ='{}'".format(
                            data['status'], data['workhour'], data['difficulty_level'], data['cg_id'],data['img_path'])  # 对r_task里的status进行更新
                        if cg_id_list:  #当主动同步时，需要更新r_task部分关联信息
                            time_cursor.execute(update_Rtask_sql)
                            #主动更新才返回数据
                            success_task_dict[data['cg_id']] = (True,
                            data['status'], data['workhour'], data['description'], data['difficulty_level'])
                            info_list.append((data['show_name'] + " 更新成功", True))

                        time_cursor.execute(update_task_sql)
                        updated_cg_id = data['cg_id']  #这是更新过的cg_id
                        updated_cg_id_list.append(updated_cg_id)


                #求出cgtw没有更新的任务，也就是找不到的任务，这些任务一般是在cgtw删除了的任务,将其设置为close
                miss_cg_id_list = list(set(all_cg_id_list)-set(updated_cg_id_list))
                print('cgtw上找不到的任务数:',len(miss_cg_id_list))
                if miss_cg_id_list:
                    for i in miss_cg_id_list:
                        set_sql = "update timeaxis.task set status = 'Close' where cg_id ='{}'".format(i)
                        try:
                            time_cursor.execute(set_sql)
                            # time_connect.commit()
                            success_task_dict[i] = (False,'Close')

                        except Exception as set_Close_Status_ERR:
                            print('设置本地状态为Close错误', i)
                            print(str(set_Close_Status_ERR))


                time_connect.commit()
            except Exception as sysc_task_ERR:
                print(str(sysc_task_ERR))
                info_list.append(("任务更新失败", False))
            finally:
                time_connect.close()
        return info_list,success_task_dict


    def resetCustomPosition1000(self):
        """
        将自定义任务为1000位置的历史任务重新设置到位置为10的地方
        :return:
        """
        time_connect, time_cursor = self.scheduler_handlder._init_timeaxis_db()
        try:
            sql="update timeaxis.custom_task set position=10 where position =1000"
            time_cursor.execute(sql)
            time_connect.commit()
            print('setAssignedFiter完成')
        except Exception as resetCustomPosition1000_ERR:
            print('resetCustomPosition1000_ERR:',str(resetCustomPosition1000_ERR))
            print('setAssignedFiter重置r_task为已分配失败')
        finally:
            time_connect.close()








    """
        ************************************************************************
        ************************************************************************

        同步所有表
            用在服务自动同步上
        ************************************************************************
        ************************************************************************
    """

    def sync_all(self):
        """
        同步cgtw所有相关数据到 排期软件 (同步顺序不能随便更改)
        :return:
        """
        active_database_project_info = self.cgtw_handler.active_database_project_info
        if not active_database_project_info:
            return
        active_databases = active_database_project_info.keys()

        try:
            print('开始同步项目信息')
            self.sync_r_project()
            print('同步项目信息完成')
        except Exception as sync_r_project_ERR:
            print('sync_r_project_ERR :',str(sync_r_project_ERR))

        try:
            print('开始同步账户信息')
            self.sync_r_account()
            print('同步账户信息完成')
        except Exception as sync_r_account_ERR:
            print('sync_r_account_ERR :',str(sync_r_account_ERR))

        try:
            print('开始同步部门信息')
            self.sync_r_department()
            print('同步部门信息完成')
        except Exception as sync_r_department_ERR:
            print('sync_r_department_ERR :', str(sync_r_department_ERR))

        try:
            print('开始清除重复任务')
            self.clear_repeat_rtask(active_databases)
            print('清除重复任务完成')
        except Exception as clear_repeat_rtask_ERR:
            print('clear_repeat_rtask_ERR :', str(clear_repeat_rtask_ERR))

        try:
            print('开始同步任务信息')
            for proj in active_databases:
                self.sync_r_tasks(proj)
            print('同步任务信息完成')
        except Exception as sync_r_tasks_ERR:
            print('sync_r_tasks_ERR :', str(sync_r_tasks_ERR))


        try:
            print('开始同步任务状态')
            self.sync_tasks(active_databases)
            print('同步任务状态完成')
        except Exception as sync_tasks_ERR:
            print('sync_tasks_ERR :', str(sync_tasks_ERR))

        try:
            print('开始重置遗失任务组任务')
            self.resetCustomPosition1000()
            print('重置遗失任务组任务完成')
        except Exception as resetCustomPosition1000_ERR:
            print('resetCustomPosition1000_ERR :', str(resetCustomPosition1000_ERR))


    """
        ************************************************************************
        ************************************************************************

        即时同步
            对排期软件选择的cgtw任务或者任务组进行同步

        ************************************************************************
        ************************************************************************
    """

    def realtime_sync_tasks(self,cg_id_list):
        """
        通过对传入的cgtw任务cg_id列表对所选择的任务进行主动同步
        :param cg_id_list:
        :return:
        """
        pass







    """
        ************************************************************************
        ************************************************************************

        任务分配

        ************************************************************************
        ************************************************************************
    """
    def assign_tasks(self,cgtw_tasks,updater):
        """
        用于任务分配而非任务更新。
        传入 cgtw_tasks,任务信息列表，插入或更新本地task表中的cgtw类型任务。
        :param cgtw_tasks: [cgtw_task_data,cgtw_task_data,...]
        :param updater: 任务分配更新者
        :return: info_list 分配结果
        """
        info_list = []
        timeaxis_connect, timeaxis_cursor = self.scheduler_handlder._init_timeaxis_db()
        assigned_time = time.strftime('%Y-%m-%d %H:%M:%S')

        for i in cgtw_tasks:#获取从分配任务数据中拿取的数据i，再向其中添加assigned_time、retake_info其他都继承于i
            i.update({'assigned_time': assigned_time})
            cg_id = i['cg_id']
            artist = i['artist']
            assigner = updater
            retake_workhour = i['retake_workhour'] if i['retake_workhour'] and i['retake_workhour'] != 'None' else ''
            retake_workhour_count = len(retake_workhour.split(',')) if retake_workhour else 0
            retake_workhour_list = retake_workhour.split(',') if retake_workhour_count > 0 else []  # 返修工时拆分后的列表
            create_history = i[
                'create_history'] if 'create_history' in i.keys() else 0  # 所有已分配任务都是0，只有从返修栏中拖出来的才是1，延期任务是2
            workhour = i['workhour']
            cgworkhour = i['cgworkhour']
            client_status_retake_count = i['client_status_retake_count']
            if client_status_retake_count == 1:  # 当是第一次返修时，就记录原cgtw上的工时
                workhour = cgworkhour
    #存入本地task表
            current_data = {}  # 需要存入retake_info中的当前数据
            current_data['artist'] = i['artist']
            current_data['position'] = i['position']
            current_data['start_date'] = i['start_date']  # 存入本地的日期不用该
            current_data['end_date'] = i['end_date']
            current_data['status'] = i['status']
            current_data['artist_level'] = i['artist_level']
            current_data['difficulty_level'] = i['difficulty_level']
            current_data['workhour'] = i['workhour']
            current_data['show_workhour'] = i['show_workhour']  # 存入当前show_workhour,因为show_workhour影响显示条长度
            current_data['description'] = i['description']
            current_data['note'] = i['note']  # 存入当前用户自定义note信息
            current_data['client_status_retake_count'] = i['client_status_retake_count']  # 把当前返修次数也返修加进去
            current_data['assigner'] = assigner
            current_data['assigned_time'] = assigned_time

            #先检测该任务是否在task中已存在，如果存在则获取其返修前所有数据(retake_info)
            try:
                search_sql = "select position,retake_info,start_date,end_date,workhour,artist,show_workhour,assigner from timeaxis.task where cg_id ='{}'".format(cg_id)
                timeaxis_cursor.execute(search_sql)
                local_task = timeaxis_cursor.fetchone()
            except:
                local_task = None

            if local_task: #存在本地任务就生成记录历史自定义任务
                print('A存在本地任务')
                _position, _retake_info, _start_date, _end_date, _workhour, _artist, _show_workhour, _assigner = local_task
                try:
                    if create_history>0:  #生成历史自定义任务，有通过历史与超时历史
                        # 补parent_data内容
                        task_data = {}
                        task_data['position'] = local_task[0] if local_task[0] !=1000 else 10  #任务组中的历史设为10
                        task_data['start_date'] = _start_date
                        task_data['end_date'] = _end_date
                        task_data['workhour'] = _workhour
                        task_data['artist'] = _artist
                        task_data['show_workhour'] = _show_workhour
                        task_data['assigner'] = _assigner
                        # 添加自定义属性,
                        task_data['task_type'] = 'custom'
                        if create_history == 1:  # 历史通过任务
                            task_data['custom_type'] = '历史'
                            task_data['color'] = '#32CD32'
                        else:  # 超时任务
                            task_data['custom_type'] = '超时'
                            task_data['color'] = '#696969'
                        insert_task = "insert into timeaxis.custom_task(cg_id,start_date,end_date,artist,workhour," \
                                      "description,task_type,custom_type,note,position," \
                                      "show_workhour,parent_data,show_name,assigned_time,department," \
                                      "project_name,color,date_length,ignore_weekend,database," \
                                      f"assigner,account_id) values ('{cg_id}','{task_data['start_date']}','{ task_data['end_date']}','{task_data['artist']}','{ task_data['workhour']}'," \
                                      f"'{i['description']}','{task_data['task_type']}','{task_data['custom_type']}','{i['note']}',{task_data['position']}," \
                                      f"'{task_data['show_workhour']}','{json.dumps(i)}','{i['show_name']}','{i['assigned_time']}','{i['department']}'," \
                                      f"'{i['project_name']}','{task_data['color']}','{i['date_length']}','{i['ignore_weekend']}','{i['database']}'," \
                                      f"'{task_data['assigner']}','{i['account_id']}')"
                        timeaxis_cursor.execute(insert_task)
                except Exception as create_history_ERR:
                    print('create_history_ERR:',create_history_ERR)

                retake_info = local_task[1]
                if not isinstance(retake_info, dict):
                    retake_info = json.loads(local_task[1])  # 此时的local_task就是一个tuple,不存在[0]再取
                if retake_info:  # 应该是必然存在retake_info的，因为初始存的时候就会带上current_data
                    client_status_retake_count = i[
                        'client_status_retake_count']  # 返修次数判断，但此时仅仅是拖入的新任务才有返修次数，本地还得需要一个返修次数字段
                    # 而且该返修字段也进入信号刷新内容
                    max_key = int(max(retake_info.keys()))  # 获取返修信息中最大值
                    # print(max_key)
                    delta_count = client_status_retake_count - max_key
                    i['retake_info'] = {}
                    if client_status_retake_count > max_key:  # 当从r_task中获取的返修次数大于retake中的最大值时，说明是新的返修任务
                        # 需要判定数量，当他们差值大于1时，表示本地返修没有漏记了。那么在retake_info前面插入缺失的模板，但不能覆盖默认'0':xx
                        print('本地返修次数小于cgtw返修次数')
                        retake_module = {"note": "", "artist": "", "status": "Retake", "assigner": "", "end_date": "",
                                         "position": 5, "workhour": "", "start_date": "", "description": "",
                                         "artist_level": "", "assigned_time": "", "show_workhour": "",
                                         "difficulty_level": "", "client_status_retake_count": 0}
                        if delta_count > 1:
                            for c in range(delta_count):  # 最新一次记录本次任务信息
                                if client_status_retake_count - c - 1 != 0:  # 排除第一次
                                    retake_info[str(client_status_retake_count - c - 1)] = retake_module
                        retake_info.update({str(max_key + 1): current_data})  # 新的返修记录
                        # 如果要删除原任务，留下通过自定义任务历史的话，如果返修次数没有等于本身返修次数，那么会出问题

                        # i['retake_info']=json.dumps(retake_info) #新的返修记录
                    elif client_status_retake_count == int(max(retake_info.keys())):  # 当返修次数与现有的一样时，就更新现有的,不自增长
                        print('本地与cgtw返修次数一致')
                        retake_info.update({str(max_key): current_data})  # 好像读回来key变成了str
                    elif client_status_retake_count < int(
                            max(retake_info.keys())):  # 当r_task返修次数小于本地task里的返修次数，那就删除掉尾部多出的
                        print('本地返修次数大于cgtw返修次数')
                        count_list = [str(c) for c in range(client_status_retake_count + 1)]
                        keys = list(retake_info.keys())
                        for k in keys:
                            if k not in count_list:
                                retake_info.pop(k)
                    if retake_workhour_count == int(
                            max(retake_info.keys())) and retake_workhour_count > 0:  # 当返修记录下的长度即返修次数确实与更新过的返修任务长度一致时
                        # 且返修次数超过0
                        for n in retake_info.keys():
                            if n != '0':  # 当非第0次时
                                retake_info[n]['workhour'] = retake_info[n]['show_workhour'] = retake_workhour_list[
                                    int(n) - 1]  # retake_info里的对应返修次数里的工时换成time_log里的工时,显示工时也需要。

                    i['retake_info'] = json.dumps(retake_info)
                    # print('最终retake_info:',retake_info)
            else:#当不是返修任务而是新任务时
                i['retake_info'] = json.dumps({"0": current_data})  # 注意使用因为读取原因还是使用字符串'0','1','2'来表示key

            try:
                update_task_sql = "INSERT INTO timeaxis.task(task_type,r_id,artist,position,start_date," \
                                  "end_date, status, cg_id,retake_info,task_name," \
                                  "task_pipeline,project_name, artist_level, difficulty_level, workhour, " \
                                  "module, description,assigned_time, eps,img_path," \
                                  "important_task_mark, show_name, assigner,database,note," \
                                  "project_shortname,show_workhour,client_status_retake_count,sort_right,assign_pipeline," \
                                  "department,project_color,retake_workhour,date_length,ignore_weekend," \
                                  "last_submit_time,separated,frame,ingroup,cgworkhour," \
                                  "assigned_times,assigned_workhour,name,deadline,account_id) VALUES " \
                                  "('{0}',{1},'{2}',{3},'{4}'," \
                                  "'{5}','{6}','{7}','{8}','{9}'," \
                                  "'{10}','{11}','{12}','{13}','{14}'," \
                                  "'{15}','{16}','{17}','{18}','{19}'," \
                                  "'{20}','{21}','{22}','{23}','{24}'," \
                                  "'{25}','{26}',{27},{28},'{29}'," \
                                  "'{30}','{31}','{32}',{33},{34}," \
                                  "'{35}',{36},'{37}',{38},'{39}'," \
                                  "{40},'{41}','{42}','{43}','{44}')on conflict(cg_id) do update set " \
                                  "task_type ='{0}',r_id={1}, artist='{2}', position={3},start_date='{4}'," \
                                  "end_date='{5}', status='{6}', cg_id='{7}', retake_info='{8}',task_name='{9}'," \
                                  "task_pipeline='{10}', project_name='{11}', artist_level='{12}', difficulty_level='{13}', workhour='{14}', " \
                                  "module='{15}', description='{16}',assigned_time='{17}', eps='{18}', img_path='{19}', " \
                                  "important_task_mark='{20}', show_name='{21}', assigner='{22}',database='{23}',note='{24}'," \
                                  "project_shortname='{25}',show_workhour='{26}',client_status_retake_count={27},sort_right={28},assign_pipeline='{29}'," \
                                  "department='{30}',project_color='{31}',retake_workhour='{32}',date_length={33},ignore_weekend={34}," \
                                  "last_submit_time='{35}',separated={36},frame='{37}',ingroup={38},cgworkhour='{39}'," \
                                  "assigned_times={40},assigned_workhour='{41}',name='{42}',deadline='{43}',account_id='{44}'". \
                    format(i['task_type'], i['r_id'], i['artist'], i['position'], i['start_date'],
                           i['end_date'], i['status'],i['cg_id'], i['retake_info'], i['task_name'],
                           i['task_pipeline'],i['project_name'], i['artist_level'],i['difficulty_level'], i['workhour'],
                           i['module'], i['description'],i['assigned_time'], i['eps'],i['img_path'],
                           i['important_task_mark'], i['show_name'], assigner,i['database'],i['note'],
                           i['project_shortname'],i['show_workhour'],i['client_status_retake_count'],i['sort_right'],i['assign_pipeline'],
                           i['department'],i['project_color'],i['retake_workhour'],i['date_length'],i['ignore_weekend'],
                           i['last_submit_time'],i['separated'],i['frame'],i['ingroup'],i['cgworkhour'],
                           i['assigned_times'],i['assigned_workhour'],i['name'],i['deadline'],i['account_id'])#i['last_submit_time']不需要加，只需要读就行，task会同步的时候加
                # #更新分配历史
                add_history_sql = "insert into timeaxis.assigned_task(task_type,cg_id,artist,show_name,start_date," \
                                  "end_date,date_length,position,assigner,assigned_time) VALUES " \
                                  f"('cgtw','{i['cg_id']}','{i['artist']}','{i['show_name']}','{i['start_date']}'," \
                                  f"'{i['end_date']}','{i['date_length']}',{i['position']},'{assigner}','{assigned_time}')" \


                timeaxis_cursor.execute(update_task_sql)
                timeaxis_cursor.execute(add_history_sql)
                timeaxis_connect.commit()


            # 本地task存完后再更新r_task表,找到同个cg_id的r_task更改assigned值和艺术家名称
                update_assigned_artist_sql = "update timeaxis.r_task set assigned ={},artist='{}' where cg_id ='{}'".format(
                    1, artist, cg_id)
                timeaxis_cursor.execute(update_assigned_artist_sql)
                timeaxis_connect.commit()
                print('更新r_task完成')
                info_list.append((i['show_name'] + " 分配成功", True))
            except Exception as TaskErr:
                print('错误信息：')
                print(str(TaskErr))
                info_list.append((i['show_name'] + " 分配失败", False))
        timeaxis_connect.close()
        return info_list


    def assign_custom_tasks(self, custom_tasks:list,assigner:str):
        """
        更新自定义任务，无则插入，有则更新
        :param custom_tasks: 自定义任务数据列表 [cdata,cdata,...]
        :param assigner: 分配者
        :return: info_list ,[(text,bool),] 返回任务信息
        """
        info_list = []
        assigned_time = time.strftime('%Y-%m-%d %H:%M:%S')
        try:
            time_connect, time_cursor = self.scheduler_handlder._init_timeaxis_db()
            for i in custom_tasks:
                sql = ''
                i['assigned_time'] = assigned_time
                if 'id' in i.keys() and i['id']:  # 如果id存在,那么表示是已经存储的自定义任务

                    sql = f"update timeaxis.custom_task set cg_id='{i['cg_id']}',start_date='{i['start_date']}',end_date='{i['end_date']}',artist='{i['artist']}',workhour='{i['workhour']}'," \
                          f"description='{i['description']}',task_type='{i['task_type']}',custom_type='{i['custom_type']}',note='{i['note']}',position={i['position']}," \
                          f"show_workhour='{i['show_workhour']}',parent_data='{json.dumps(i['parent_data'])}',show_name='{i['show_name']}',assigned_time='{ i['assigned_time']}',department='{i['department']}'," \
                          f"project_name='{i['project_name']}',color='{i['color']}',date_length={i['date_length']},ignore_weekend={i['ignore_weekend']},font_color='{i['font_color']}'," \
                          f"database='{i['database']}',assigner='{assigner}',status='{i['status']}',account_id= '{i['account_id']}' where id ={i['id']}"

                else:  # id不存在，那么表示非子任务
                    sql = "insert into timeaxis.custom_task (cg_id,start_date,end_date,artist,workhour," \
                          "description,task_type,custom_type,note,position," \
                          "show_workhour,parent_data,show_name,assigned_time,department," \
                          "project_name,color,date_length,ignore_weekend,font_color," \
                          "database,assigner,status,account_id) values " \
                          f"('{i['cg_id']}','{i['start_date']}','{i['end_date']}','{i['artist']}','{i['workhour']}'," \
                          f"'{i['description']}','{i['task_type']}','{i['custom_type']}','{i['note']}',{i['position']}," \
                          f"'{i['show_workhour']}','{json.dumps(i['parent_data'])}','{i['show_name']}','{i['assigned_time']}','{i['department']}'," \
                          f"'{i['project_name']}','{i['color']}',{i['date_length']},{i['ignore_weekend']},'{i['font_color']}'," \
                          f"'{i['database']}','{assigner}','{i['status']}','{i['account_id']}')"

                    # print('custom sql:', sql)
                try:
                    # #更新历史
                    add_history_sql = "insert into timeaxis.assigned_task(task_type,cg_id,artist,show_name,start_date," \
                                      "end_date,date_length,position,assigner,assigned_time) VALUES " \
                                      f"('custom','{i['cg_id']}','{i['artist']}','{i['show_name']}','{i['start_date']}'," \
                                      f"'{i['end_date']}','{i['date_length']}',{i['position']},'{assigner}','{assigned_time}')"

                    time_cursor.execute(sql)
                    time_cursor.execute(add_history_sql)
                    info_list.append((i['show_name'] + " 写回成功", True))
                    # print('custom console')
                    # self.console._add_info(i['show_name'] + " 写回成功", True)
                    # print('自定义任务写回成功')

                except Exception as updateCustomTask_ERR:
                    # self.console._add_info(i['show_name'] + " 写回失败", False)
                    # print('写回自定义任务失败')
                    print(str(updateCustomTask_ERR))
                    info_list.append((i['show_name'] + " 写回失败", False))

            time_connect.commit()
        except Exception as _update_custom_task_ERR:
            print(str(_update_custom_task_ERR))
        finally:
            time_connect.close()

        return info_list

    def assign_group_tasks(self, group_data_list:list,assigner:str):
        """
        存储任务组信息，不需要把task_data也存入，只存入正常的data部分，task_data再前面已划分为cgtw_task
        任务组存储机制：
            1.只存储cgtw任务
            2.只存储cgtw的任务id与任务名称
         任务组读取机制:
            通过存储的cg_id来读取任务，当该cgtw任务的索引不再为1000时，就不读取

        :param group_data_list:
        :return:
        """
        info_list = []
        timeaxis_connect, timeaxis_cursor = self.scheduler_handlder._init_timeaxis_db()  # 用于本地数据timeaxis的操作
        assigned_time = time.strftime('%Y-%m-%d %H:%M:%S')
        for data in group_data_list:
            i = data['data']
            i['assigned_time'] = assigned_time
            sql = ''
            if 'id' in i.keys():  # and i['task_id_dict']:  # 如果id存在,那么表示是已经存储的自定义任务
                print('重载的Group_task')
                # print(data)
                sql = f"update timeaxis.group_task set group_name='{i['group_name']}',color='{i['color']}',project_name='{i['project_name']}',department='{i['department']}',start_date='{i['start_date']}'," \
                      f"end_date='{i['end_date']}',date_length='{i['date_length']}',workhour='{i['workhour']}',artist='{i['artist']}',position={i['position']}," \
                      f"artist_level='{i['artist_level']}',note='{i['note']}',assigned_time='{i['assigned_time']}',task_id_dict='{json.dumps(i['task_id_dict'])}',tasks='{i['tasks']}'," \
                      f"use_custom_workhour={i['use_custom_workhour']},database='{i['database']}',account_id='{i['account_id']}'  where id ={i['id']}"

            else:  # 'id' not in i.keys() and i['task_id_dict']:  # id不存在，那么表示非子任务
                print('新任务组')
                sql = "insert into timeaxis.group_task (group_name,color,project_name,department,start_date," \
                      "end_date,date_length,workhour,artist,position," \
                      "artist_level,note,assigned_time,task_id_dict,tasks," \
                      "use_custom_workhour,database,account_id) values " \
                      f"('{i['group_name']}','{i['color']}','{i['project_name']}','{i['department']}','{i['start_date']}'," \
                      f"'{i['end_date']}','{i['date_length']}','{i['workhour']}','{i['artist']}',{i['position']}," \
                      f"'{i['artist_level']}','{i['note']}','{i['assigned_time']}','{json.dumps(i['task_id_dict'])}','{i['tasks']}'," \
                      f"{i['use_custom_workhour']},'{i['database']}','{i['account_id']}')"

            try:
                timeaxis_cursor.execute(sql)
                print('任务组写回成功')
                info_list.append((i['group_name'] + " 写回任务组成功", True))
            except Exception as updateGroupTask_ERR:
                print('写回任务组失败')
                print(str(updateGroupTask_ERR))
                info_list.append((i['group_name'] + " 写回任务组失败", False))

        timeaxis_connect.commit()
        timeaxis_connect.close()

        return info_list

    """
        ************************************************************************
        ************************************************************************

        任务删除

        ************************************************************************
        ************************************************************************
    """

    def delete_tasks(self,data_list:list,deleter:str):
        """
        删除传入数据的任务
        :param data_list: 数据列表，可以是cgtw,custom,group类型任务
        (task_type, group_name, connect_cg_id, start_date, end_date, artist, position,group_id,children_names)
        :param deleter:  删除者
        :return:
        """
        info_list = []
        if data_list:
            timeaxis_connect, timeaxis_cursor = self.scheduler_handlder._init_timeaxis_db()
            cgtw_connect, cgtw_cursor = self.cgtw_handler._init_cgtw_db()
            deleted_by = deleter
            delete_time = time.strftime('%Y-%m-%d %H:%M:%S')
            # print('开始删除任务05.2')
            for i in data_list:
                task_type = i[0]
                show_or_group_name = i[1]  # 当是group类型时是group_name 否则就是task的show_name
                cg_id_or_connect_cg_id = i[2]  # 当是group类型时，是所有的拼接cg_id,否则就是cg_id
                start_date = i[3]
                end_date = i[4]
                artist = i[5]
                position = i[6]
                custom_task_id = i[7]
                children_names = i[8]
                # print('开始删除任务05.51')
                delete_cgtw_sql = "delete from timeaxis.task where cg_id ='{}'".format(cg_id_or_connect_cg_id)
                update_Rtask_sql = "update timeaxis.r_task set assigned = 0 where cg_id ='{}'".format(
                    cg_id_or_connect_cg_id)
                delete_custom_sql_id = "delete from timeaxis.custom_task where id = '{}'".format(custom_task_id)
                delete_custom_sql = "delete from timeaxis.custom_task where show_name = '{}' and start_date='{}' and end_date ='{}' and artist='{}' and position={}".format(
                    show_or_group_name, start_date, end_date, artist, position)
                delete_group_sql = "delete from timeaxis.group_task where group_name='{}' and start_date='{}' and artist='{}' and position={}".format(
                    show_or_group_name, start_date, artist, position)
                delete_group_tasks_sql = "delete from timeaxis.task where cg_id similar to '({})'".format(
                    cg_id_or_connect_cg_id)
                update_group_rtask_sql = "update timeaxis.r_task set assigned =0 where cg_id similar to '({})'".format(
                    cg_id_or_connect_cg_id)

                if task_type == 'cgtw':
                    cgtw_history_sql = "insert into timeaxis.delete_task(task_type,cg_id,artist,show_name,start_date," \
                                       "end_date,position,deleted_by,delete_time)" \
                                       " values ('{0}','{1}','{2}','{3}','{4}','{5}',{6},'{7}','{8}')". \
                        format('cgtw', cg_id_or_connect_cg_id, artist, show_or_group_name, start_date, end_date, position,
                               deleted_by, delete_time)
                    try:
                        timeaxis_cursor.execute(delete_cgtw_sql)#删除本地task任务
                        timeaxis_cursor.execute(update_Rtask_sql)#重置r_task任务
                        timeaxis_cursor.execute(cgtw_history_sql)  # 记录删除任务信息
                        timeaxis_connect.commit()
                        info_list.append((show_or_group_name + " cgtw任务删除成功",True))
                        try:
                            database, module, task_id = cg_id_or_connect_cg_id.split(',')
                            self.clear_cgtw_dates(cgtw_cursor, database, task_id)
                            cgtw_connect.commit()
                            info_list.append((show_or_group_name + " 清除cgtw日期成功", True))
                        except Exception as clear_cgtw_dates_ERR:
                            print('清空{}日期失败'.format(show_or_group_name))
                            print(str(clear_cgtw_dates_ERR))
                            info_list.append((show_or_group_name + " 清除cgtw日期失败", False))
                    except Exception as delete_cgtw_ERR:
                        print('删除本地任务失败')
                        print(str(delete_cgtw_ERR))
                        info_list.append((show_or_group_name + " 本地任务删除失败", False))

                elif task_type == 'custom':
                    custom_history_sql = "insert into timeaxis.delete_task(task_type,cg_id,artist,show_name,start_date," \
                                         "end_date,position,deleted_by,delete_time)" \
                                         " values ('{0}','{1}','{2}','{3}','{4}','{5}',{6},'{7}','{8}')". \
                        format('custom', '', artist, show_or_group_name, start_date, end_date, position,
                               deleted_by, delete_time)
                    try:
                        c_sql = delete_custom_sql_id if custom_task_id else delete_custom_sql
                        timeaxis_cursor.execute(c_sql)
                        timeaxis_cursor.execute(custom_history_sql)  # 自定义任务删除保存历史
                        info_list.append((show_or_group_name + " custom任务删除成功", True))
                    except Exception as delete_custom_ERR:
                        print('删除自定义任务失败')
                        print(str(delete_custom_ERR))
                        info_list.append((show_or_group_name + " 删除失败", False))

                elif task_type == 'group':
                    group_history_sql = "insert into timeaxis.delete_task(task_type,cg_id,artist,show_name,start_date," \
                                        "end_date,position,deleted_by,delete_time)" \
                                        " values ('{0}','{1}','{2}','{3}','{4}','{5}',{6},'{7}','{8}')". \
                        format('group', '', artist, children_names, start_date, end_date, position,
                               deleted_by, delete_time)
                    try:
                        timeaxis_cursor.execute(delete_group_sql)  # 先删除本地group_task任务
                        if cg_id_or_connect_cg_id:
                            timeaxis_cursor.execute(delete_group_tasks_sql)  # 删除本地task任务
                            timeaxis_cursor.execute(update_group_rtask_sql)  # 重置r_task任务
                            timeaxis_cursor.execute(group_history_sql)  # 删除的任务组写入delete_task历史记录当中
                            try:  # 清空cgtw上日期
                                cg_id_list = cg_id_or_connect_cg_id.split('|')
                                for c in cg_id_list:
                                    database, module, task_id = c.split(',')
                                    self.clear_cgtw_dates(cgtw_cursor, database, task_id)
                                cgtw_connect.commit()
                                info_list.append((show_or_group_name + " 清除任务组内cgtw任务日期成功", True))
                            except Exception as clear_group_cgtw_dates_ERR:
                                print('清空任务组{}内cgtw任务日期失败'.format(show_or_group_name))
                                print(str(clear_group_cgtw_dates_ERR))
                                info_list.append((show_or_group_name + " 清除任务组内cgtw任务日期失败", False))
                        info_list.append((show_or_group_name + " 任务组删除成功", True))
                    except Exception as delete_group_ERR:
                        print('删除任务组失败')
                        print(str(delete_group_ERR))

            timeaxis_connect.commit()
            timeaxis_connect.close()
            cgtw_connect.close()
        return info_list

    def clear_cgtw_dates(self,cgtw_cursor,database,task_id):
        """
        清除cgtw里日期
        :param cgtw_cursor:
        :param database:
        :param task_id:
        :return:
        """
        select_sql="select json_data from {}.entity where id ='{}'".format(database,task_id)
        cgtw_cursor.execute(select_sql)
        cg_taskdata = cgtw_cursor.fetchone()  # 获取查询到的cgtw的对应任务信息
        if cg_taskdata:
            jd = cg_taskdata[0]  # 获取json_data
            jd['start_date']='' #清掉日期
            jd['end_date']=''
            update_sql="update {}.entity set json_data='{}' where id ='{}'".format(database,json.dumps(jd),task_id)
            cgtw_cursor.execute(update_sql)




    """
    ***********************************************
    ***********************************************

    写回cgtw

    ***********************************************
    ***********************************************
    """
    def update_cgtw(self,cgtw_tasks,updater):
        """
        更新cgtw,传入的须是cgtw类型
        :param: cgtw_tasks,传入TaskItem中的数据信息列表
        :param: updater 更新人员
        :return:
        """
        info_list = []
        cg_conn, cg_sess = self.cgtw_handler._init_cgtw_db()#每次更新cgtw时,都重新开一个连接，之后关闭掉
        timeaxis_connect, timeaxis_cursor = self.scheduler_handlder._init_timeaxis_db()

        assigned_time = time.strftime('%Y-%m-%d %H:%M:%S')
        for i in cgtw_tasks:
            schema = i['database']
            task_id = i['cg_id'].split(',')[2]
            select_sql = "select json_data from {0}.entity where id='{1}'".format(schema, task_id)

            # 获取retake_info
            cg_id = i['cg_id']
            artist = i['artist']
            assigner = updater

            #今日起，返修数据不再写回cgtw,仅更新需要更新的信息，尽量不新增字段输入

            try:#更新history，cgtw历史，要过滤掉同人名记录
                print('B005')
                database=i['database']
                new_id='MZ'+str(random.randint(1,10000))+str(time.time())+str(random.randint(1,10000))
                account_id='xxxxx'
                history_data={"file": "","step": "","text": "PAssign to: xx","time": "2020-01-15 19:28:58","module": "shot",
                              "status": "Assign","create_by": "xx","undo_data": "null","create_time": "2020-01-15 17:28:58",
                              "module_type": "task","status_type": "assign","last_update_by": "xx",
                              "last_update_time": "2020-01-15 19:28:58"}
                history_data['text'] = "PAssign to:{}".format(i['artist'])
                history_data['time'] = time.strftime('%Y-%m-%d %H:%M:%S')
                history_data['module'] = i['module']
                history_data['create_by'] = assigner
                history_data['assign_artist'] = assigner
                update_history_sql = "INSERT INTO {}.history(id,link_id, json_data) VALUES ('{}','{}','{}')".format(
                    database,
                    new_id, task_id, json.dumps(history_data))

                # 更新entity
                cg_sess.execute(select_sql)  # 这里先正常执行获取cgtw任务信息
                cg_taskdata = cg_sess.fetchone()  # 获取查询到的cgtw的对应任务信息
                if cg_taskdata:
                    jd = cg_taskdata[0]  # 获取json_data
                    orient_artist = jd['artist']
                    orient_assigner = jd['assign_artist'] if 'assign_artist' in jd.keys() else ''

                    jd['artist'] = i['artist']  # 更新制作者
                    jd['start_date'] = i['start_date']+' 00:00:00'#cgtw日期格式化才能正确显示
                    jd['end_date'] = i['end_date']+' 00:00:00'
                    jd['assign_artist'] = assigner
                    jd['account_id'] = i['account_id']
                    update_sql = "update {0}.entity set json_data='{1}' where id='{2}'".format(schema,
                                                                                             json.dumps(jd),
                                                                                             task_id)
                    cg_sess.execute(update_sql)  # 更新日期操作
                    if orient_artist != i['artist'] or assigner != orient_assigner:  # 当制作者不一致或者分配者不一致时才更新历史
                        cg_sess.execute(update_history_sql)  # 更新历史操作
                        info_list.append((i['show_name'] + " 写回cgtw history成功", True))
                        print('任务已换人，记录历史')
                    else:
                        print('无需更新history')
                        info_list.append((i['show_name'] + " 无需更新history", True))

            except Exception as toCTGW_err:
                print('读取cgtw对应任务或者写入cgtw任务错误')
                print(str(toCTGW_err))
                info_list.append((i['show_name'] + " 写回cgtw失败", False))

        cg_conn.commit()
        cg_conn.close()

        return info_list




    """
        ************************************************************************
        ************************************************************************
        ************************************************************************
        ************************************************************************
        AD广告栏数据据操作
        ************************************************************************
        ************************************************************************
        ************************************************************************
        ************************************************************************
    """

    def ad_get_data(self,_type:int):
        """
        传入模板索引，查询该模块下所有任务信息
        :param _type:
        :return:
        """
        data = self.ad_handler.get_data(_type)

        return data

    def ad_set_data(self,data):
        """
        添加广告任务
        :param data:
        :return:
        """

        success = self.ad_handler.set_data(data)

        return success

    """
        ************************************************************************
        ************************************************************************
        ************************************************************************
        ************************************************************************
        一次性补全操作
        ************************************************************************
        ************************************************************************
        ************************************************************************
        ************************************************************************
    """
    def sync_central_task_module(self):
        """
        中央数据库中表taskhistory新增module字段，从cgtw6数据库中同步过去
        :return:
        """
        c_connect, c_cursor = self.chandler._init_post_db()  # 中央数据库
        connect, cursor = self.cgtw_handler._init_cgtw_db()  # cgtw数据库

        select_sql = "select id,database,task_id from timeaxis.taskhistory"
        try:
            c_cursor.execute(select_sql)
            result = c_cursor.fetchall()
            if result:
                for i in result:
                    _id,database,task_id = i
                    sql = f"select module from {database}.entity where id = '{task_id}'"
                    cursor.execute(sql)
                    _res = cursor.fetchone()
                    module = 'shot'
                    if _res:
                        module = _res[0]
                    update_sql = f"update timeaxis.taskhistory set module = '{module}' where id = {_id}"
                    c_cursor.execute(update_sql)
                c_connect.commit()
                print('同步完成')
        except Exception as sync_central_task_module_ERR:
            print('sync_central_task_module_ERR:',sync_central_task_module_ERR)
        finally:
            c_connect.close()
            connect.close()

    """
        ************************************************************************
        ************************************************************************
        ************************************************************************
        ************************************************************************
        数据转换
        ************************************************************************
        ************************************************************************
        ************************************************************************
        ************************************************************************
    """
    def test_get_ori_all_data(self):
        result = self.scheduler_handlder.test_get_ori_data()
        print(type(result))
        print(result)


    def get_table_colums(self,table_name,schema):
        """
        获取指定表的所有列名称
        :param table_name:
        :param schema:
        :return:
        """
        columns = self.scheduler_handlder.get_table_colums(table_name, schema)
        return columns

    def table2pd(self,table_name,columns_names=None,schema='timeaxis'):
        """
        获取指定的table表中的所有数据，并转成DataFrame
        :param table_name:
        :param columns_names: 表中列的名称 列表 []
        :param schema:
        :return:

        >>> df=handler.table2pd('projects',columns_names = ['id','project_name','database'],schema='timeaxis')
        >>> print(df.head())

        """
        import pandas as pd
        if columns_names:
            data=self.scheduler_handlder.get_table_data_by_column(table_name,columns_names)
        else:
            data=self.scheduler_handlder.get_table_data(table_name,schema)
            columns_names=self.scheduler_handlder.get_table_colums(table_name,schema)
        df = pd.DataFrame(data,columns=columns_names)
        return df


    def exportDesription(self,dirpath):
        import os
        import pandas as pd
        active_databases = self.cgtw_handler.active_database_project_info.keys()
        # print(active_databases)
        columns = ['description', 'frame', 'assign_pipeline', 'shot_difficulty_level', 'module',
                                      'task_name','task_pipeline','database','difficulty_level','workhour']
        for database in active_databases:
            csv_path = os.path.join(dirpath,database+'.csv')
            results = self.scheduler_handlder.getDescriptionByDatabase(database)
            df = pd.DataFrame(results,columns=columns)
            df.to_csv(csv_path,index=False)
            print(f'{database} 完成')

if __name__ == '__main__':
    handler=Handler()
    department_artist=handler.get_department_artists()
    print(department_artist)